package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import helperclasses.Bill;
import helperclasses.DbUtil;
import helperclasses.MyDataColector;

public class ModifyBill {
	private Bill myBill;
	private MyDataColector myDataColector;

	public ModifyBill(Bill myBill) {
		this.myBill = myBill;
		myDataColector = new MyDataColector();
	}

	private Connection getConnection() throws SQLException {

		DataSource ds = DbUtil.getInstance().getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}

	public boolean SaveBill() throws SQLException {
		boolean succes = true;
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql = "", sql1 = "", sql2 = "", sql3 = "";
		String isType1 = "no", isType2 = "no";
		int productid = 0, tip_nir = 0, facturaid = 0, furnizorid, gestionarid, companyid, restaurantid;
		double pret_iesire = 0, valoare = 0;
		ResultSet result;

		furnizorid = myDataColector.getFurnizorId(myBill.getFurnizor());
		gestionarid = myDataColector.getGestionarId(myBill.getGestionar());
		companyid = myDataColector.getCompanyId(myBill.getCompany());
		restaurantid = myDataColector.getRestaurantId(myBill.getRestaurant());

		try {
			conn.setAutoCommit(false);

			// First we insert a new entry in facturi table
			sql1 = "INSERT INTO facturi (factura_id, cod_factura, data_factura, numar_nir, furnizor_id, gestionar_id, company_id, restaurant_id) "
					+ "VALUES(null, '"
					+ myBill.getCodFactura()
					+ "', '"
					+ myBill.getDataFactura()
					+ "', "
					+ myBill.getNirNumber()
					+ ", "
					+ furnizorid
					+ ", "
					+ gestionarid
					+ ", "
					+ companyid
					+ ", "
					+ restaurantid
					+ ")";

			// We get the recently inserted bill id.
			st.executeUpdate(sql1, Statement.RETURN_GENERATED_KEYS);
			result = st.getGeneratedKeys();
			if (result.next()) {
				facturaid = result.getInt(1);
			}

			// We must loop through the bills products and insert / update each
			// one of them
			for (int i = 0; i < myBill.getProducts().size(); i++) {

				// Select product id for each product
				sql = "SELECT produs_id, valoare, tip_nir, pret_iesire FROM produse WHERE nume='" + myBill.getProducts().get(i).getProductName() + "'";

				result = st.executeQuery(sql);
				while (result.next()) {
					productid = Integer.parseInt(result.getString("produs_id"));
					valoare = Double.parseDouble(result.getString("valoare"));
					tip_nir = Integer.parseInt(result.getString("tip_nir"));
					pret_iesire = Double.parseDouble(result.getString("pret_iesire"));
				}
				if (tip_nir == 1 || tip_nir == 4) {
					isType1 = "yes";
				} else if (tip_nir == 2) {
					isType2 = "yes";
				}
				// 1. First we insert in the nota_intrare table each product
				// with the facturaid
				sql2 = "INSERT INTO nota_intrare (factura_id_fk, produs_id_nir_fk, cantitate, pret_intrare, pret_iesire) VALUES(" + facturaid
						+ ", " + productid + ", " + myBill.getProducts().get(i).getCantitate() + ", "
						+ myBill.getProducts().get(i).getPretIntrare() + ", " + pret_iesire + ")";

				st.executeUpdate(sql2);

				// 2. We update the stock with the values inserted
				sql3 = "UPDATE produse_in_stoc SET cantitate=cantitate+" + myBill.getProducts().get(i).getCantitate()
						+ ", valoare_in_stoc=valoare_in_stoc+" + (myBill.getProducts().get(i).getCantitate() * valoare) +" WHERE produs_id=" + productid;

				st.executeUpdate(sql3);
			}
			sql1 = "UPDATE facturi SET is_type1='" + isType1 + "', is_type2='" + isType2 + "' WHERE factura_id=" + facturaid;
			st.executeUpdate(sql1);
			// if all goes well we commit
			conn.commit();

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				// An error occured so we rollback the changes.
				conn.rollback();
				succes = false;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		} finally {
			st.close();
			conn.close();
		}

		return succes;
	}
}
